package com.sunlands.zlcx.usercenter.repository;

import com.sunlands.zlcx.usercenter.domain.UsersDO;
import com.sunlands.zlcx.usercenter.vo.UsersVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.Date;
import java.util.List;

/**
 * @author anjl
 * @date 1:30 PM 2019/4/15
 * description:
 */
@Repository
public interface UsersRepository extends JpaRepository<UsersDO, Long>, JpaSpecificationExecutor<UsersDO> {
    /**
     * @param name name
     * @return List
     */
    List<UsersDO> findByName(String name);

    /**
     * @param phone phone
     * @return UsersDO
     */
    UsersDO findByPhone(String phone);

    /**
     * @param idList idList
     */
    List<UsersDO> findByIdInOrderByNameAsc(List<Long> idList);

    UsersDO findByUnionId(String unionId);

    List<UsersDO> findByType(Integer type);

    @Query(value = "select u.* from tz_user u, " +
            "(select user_id,sum(study_minutes) as mins from tz_learn_timing where count_time  BETWEEN  ?1 and ?2 GROUP BY user_id order by  sum(study_minutes)  desc  limit 1000) l " +
            "where  u.id = l.user_id and u.type =1 " +
            "ORDER BY l.mins desc limit 200", nativeQuery = true)
    List<UsersDO> findUsersPool(String yesterday, String today);

    @Query(value = "select * from \n" +
            "(select * from tz_user where id not in ?1 and type = ?2  and sex <> ?3 and current_learn_target_id = ?4\n" +
            "union all \n" +
            "select * from tz_user where id not in ?1 and type = ?2  and sex <> ?3 and current_learn_target_id <> ?4\n" +
            "union all \n" +
            "select * from tz_user where id not in ?1 and type = ?2  and sex = ?3 and current_learn_target_id = ?4\n" +
            "union all \n" +
            "select * from tz_user where id not in ?1 and type = ?2  and sex = ?3 and current_learn_target_id <> ?4) s\n" +
            "where if(?7 !='',create_time < ?7, 1=1) order by create_time desc limit ?5, ?6", nativeQuery = true)
    List<UsersDO> findHomePageUsers(List<Long> userIdList, Integer type, Integer sex, Long currentLearnTargetId, Integer page, Integer pageSize, String createTime);

    @Query(value = "select * from \n" +
            "(select * from tz_user where id not in ?1 and type = ?2  and sex <> ?3 \n" +
            "union all \n" +
            "select * from tz_user where id not in ?1 and type = ?2  and sex <> ?3 \n" +
            "union all \n" +
            "select * from tz_user where id not in ?1 and type = ?2  and sex = ?3 \n" +
            "union all \n" +
            "select * from tz_user where id not in ?1 and type = ?2  and sex = ?3 ) s\n" +
            "where if(?6 !='',create_time < ?6, 1=1) order by create_time desc limit ?4, ?5", nativeQuery = true)
    List<UsersDO> findHomePageUsersNoTarget(List<Long> userIdList, Integer type, Integer sex, Integer page, Integer pageSize, String createTime);

    @Query(value = "select count(1) from  tz_user where type = ?1 and id not in ?2", nativeQuery = true)
    Integer countByTypeExcludeUserId(Integer type, List<Long> userIdList);

    List<UsersDO> findByCreateTimeBetween(Date start, Date end);

    @Query(value = "select id from  tz_user where online = ?1 ", nativeQuery = true)
    List<Long> findByOnline(Integer online);

    @Query(value = "select max(id) from  tz_user")
    Integer maxUserId();

    Page<UsersDO> findAllByNameLikeAndIdIsNot(String name, Long userId, Pageable pageable);

    @Query(value = "select t.user_id " +
            "from tz_user_day_learn t  " +
            "where  t.create_time  between ?3 and ?4 " +
            "group by t.user_id order by sum(t.study_minutes) desc limit ?1, ?2 ", nativeQuery = true)
    List<Integer> learnTimeTopUsers(int start, int pageSize, Date startTime, Date endTime);


    @Modifying
    @Query(value = "update tz_user m set m.vip_status=?2 where  m.id in (?1)", nativeQuery = true)
    void updateVipStatus(List<Long> ids, Integer vipStatus);

    @Modifying
    @Query(value = "update tz_user m set m.online=?2,m.top_run=?3,m.channel_id=?4,m.back_run_time=?5  where m.id = ?1 and (channel_id <= ?4 or channel_id is null) ", nativeQuery = true)
    Integer updateOnlineById(Long userId, Integer online, Integer topRun, Long channelId, String backRunTime);



    @Query(value = "select * from  tz_user m where m.status!=-2 and  m.device_id in (?1)", nativeQuery = true)
    List<UsersDO> findByLimitDeviceUser(List<String> ids);

    List<UsersDO> findByIdInAndOnline(List<Long> userIds, int code);
}

